Now before we can really take advantage of the power pivot tool and start to create pivot tables based
off of multiple sources with relationships we need to create what's called a data model.
And really at its surface all the data model is is a list whether it's a single list such as customer
info or it's multiple lists like customer and phone order info and a relationship built between those
lists at its surface.
Again that's all it is.
It's just the list.
So take a look at how we can create a data model that contains our two lists order info and customer
info just the two tabs down below and create a relationship between those two lists within our data
model.
All right.
So first that I'm going to go to my customer Info tab and I'm going to go to my power pivot tab at the
top of my screen.
Now on my newly installed power pivot tab I've got a few features here.
I've got things I can utilize to build to work with this list as a data model.
Well my first step is I need to add this list to my data model.
So up on the power pivot tab I've got a section called tables and I'm going to click on Add to data
model.
I'll give that a click.
And I've now created a data model that contains a single list a single set of data.
Now when I click that button it does open up a completely separate windows from Excel.
This is my power pivot for Excel tool.
And it's out of here it's got its own ribbon with its own tools.
I've got an exact duplicate of the list that lives back inside of my Excel worksheet.
It's just the link it's a link from that Excel document into power pivot into this data model.
Now this is just one source and I need to go back into Excel.
So let me just pop back into Excel here.
I'm going to go to my order Info tab and I need to add this one to the data model as well.
So all click into my list.
I'm going to go back up to my power pivot tab and I'm going to add this one to the data model as well
.
I'll give that a click.
So take just a moment and then I'll just maximize my power pivot window.
I've now got the bald customer info and order info.
As lists and side of this Excel power pivot data model.
So they're both inside of here.
Here's my customer info.
It's and again an exact duplicate of the data that lives back inside of my Excel document.
Then there's my order info.
If you watch the introductory video to this power pivot tool we saw the Alph key customer ID inside
of their exact duplicate.
This is the order information.
Now these are two different sets of data.
Two different lists.
All part of one data model.
Now we can work with these lists together.
But in order to do that and do it effectively I need to create a relationship between these two lists
.
I need to tell it how customers is related to their specific orders.
Well in the previous lecture we talked about how each customer contains a customer id Alph key and a
tar and taun and so on.
Just some type of abbreviation of their company name and the order info also contains the customer ID
field with all those same values.
So we've got this common field between these two datasets that we can now link together.
Now just enough why.
If you're a Microsoft Access user or some type of database user you're probably familiar with this concept
.
If you're not database users Microsoft Access user this might be a bit foreign to you.
The idea though is we have a common value common field between bulk data sources that we can now link
through.
So let's take a look at how we can create the relationship or the link between these two datasets.
So NL inside of my power pivot tool I need to change the way I'm viewing my data model on the home tab
top of my screen far right.
You got a section called view and I'm currently looking at it as data view it's much like Excel.
I'm looking at the data sets well just to the right of that I've got my diagram view and I'll give that
a click.
This is going to take me into what they refer to as diagram view and where I can see the two different
datasets or the two different tables.
Just expanding these out so I can see all of the fields.
This is just all the column headers all the fields that live in those two lists and I can find inside
the customer info table.
I've got customer ID and in the order info table I've got customer ID.
So now I'm going to create the relationship.
All I'm going to do is grab custom Radie left click from one table and drag it to customer ID in the
other table and I'll let go give it just a moment and I have now created a relationship between these
two tables.
It's as simple as that.
I added the data sets to the data model through power pivot.
I've got two different data in their customer info order info.
I can go back to data view to build to see this data.
Once I got the set in the data model I can then create the relationships.
I can go to diagram view just click and drag.
You're grabbing those common fields that exist inside of both datasets and just linking to drag and
drop.
You've created the relationship so we've now created a data model.
I can close out of the Excel power pivot tool.
Get back into Excel now behind the scenes.
We've got this data model that I can now start to report off of.
And that's what we're going to talk about next.
But first try this out.
Open up the exercise file.
Create the data model through your power pivot tool.
Just add the customer info.
Add the order info to your data model and then go into the diagram view and create the relationship
.
So try it out first and then we'll take a look at how we can create the pivot table based off of that
data model.